﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Net;
using System.IO;
using System.Text;
using System.Threading;
using AKResources;

namespace S3Website
{
    public class WowItem
    {
        private string name;
        private string weight;
        private string ilvl;
        private string wowheadid;

        public string Name
        {
            get
            {
                return name;
            }
        }

        public string Weight
        {
            get
            {
                return weight;
            }
        }

        public string Ilvl
        {
            get
            {
                return ilvl;
            }
        }

        public string WowheadID
        {
            get
            {
                return wowheadid;
            }
        }

        public WowItem()
        {
        }

        public WowItem(XmlNode node)
        {
            XmlDoc temp = new XmlDoc();
            temp.LoadXml(node.OuterXml);
            name = temp.GetNodeValue("//name");
            ilvl = temp.GetNodeValueIfExists("//ilvl");
            weight = temp.GetNodeValue("//weight");
            wowheadid = temp.GetNodeValue("//wowheadID");
        }
    }

    public partial class test : System.Web.UI.Page
    {
        private string GetHtml(string url)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            // Set some reasonable limits on resources used by this request
            request.MaximumAutomaticRedirections = 4;
            request.MaximumResponseHeadersLength = 4;
            // Set credentials to use for this request.
            request.Credentials = CredentialCache.DefaultCredentials;
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();

            // Get the stream associated with the response.
            Stream receiveStream = response.GetResponseStream();

            // Pipes the stream to a higher level stream reader with the required encoding format. 
            StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);

            string result = readStream.ReadToEnd();
            response.Close();
            readStream.Close();
            return result;
        }

        private void LoadIntoTankDB(string itemsURL)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");

            List<int> boots = new List<int>();
            string bootstring = GetHtml(itemsURL);
            while (bootstring.IndexOf("_[") > 0)
            {
                int start = bootstring.IndexOf("_[");
                bootstring = bootstring.Substring(start + 2);
                int end = bootstring.IndexOf("]");
                boots.Add(Int32.Parse(bootstring.Substring(0, end)));
            }

            for (int i = 0; i < boots.Count; i++)
            {
                string itemurl = "http://www.wowhead.com/?item=" + boots[i] + "&xml";
                string itemXml = GetHtml(itemurl);
                //now parse values from it
                XmlDoc itemDoc = new XmlDoc();
                itemDoc.LoadXml(itemXml);

                int wowheadID = boots[i];
                string name = itemDoc.GetNodeValue("//name").Replace("'", "");
                string bodypart = itemDoc.GetNodeValue("//inventorySlot");
                string ilvl = itemDoc.GetNodeValue("//level");
                int armor = 0;
                int hit = 0;
                int strength = 0;
                int stamina = 0;
                int dodge = 0;
                int parry = 0;
                int defense = 0;
                int red = 0;
                int yellow = 0;
                int blue = 0;
                int colourless = 0;
                int bonus_hit = 0;
                int bonus_strength = 0;
                int bonus_stamina = 0;
                int bonus_dodge = 0;
                int bonus_parry = 0;
                int bonus_defense = 0;
               

                string dataValString = itemDoc.GetNodeValue("//jsonEquip");
                string[] dataVals = dataValString.Split(new char[] { ',' });
                for (int j = 0; j < dataVals.Length; j++)
                {
                    string[] keyValuePair = dataVals[j].Split(new char[] { ':' });
                    switch (keyValuePair[0])
                    {
                        case ("armor"):
                            {
                                armor = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("hitrtng"):
                            {
                                hit = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("str"):
                            {
                                strength = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("sta"):
                            {
                                stamina = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("dodgertng"):
                            {
                                dodge = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("parryrtng"):
                            {
                                parry = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("defrtng"):
                            {
                                defense = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        default:
                            {
                                break;
                            }
                    }
                }

                //now get socket bonus and sockets
                string toolTipString = itemDoc.GetNodeValue("//htmlTooltip");
                red = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Red Socket"));
                blue = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Blue Socket"));
                yellow = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Yellow Socket"));
                int meta = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Meta Socket"));
                if (bodypart == "Waist") //belt buckle
                {
                    colourless++;
                }
                if (meta > 0) //assume ember skyflare. Ignore meta requirements.
                {
                    stamina +=35;
                }
                int bstart = toolTipString.IndexOf("Socket Bonus: +");
                if (bstart > 0)
                {
                    toolTipString = toolTipString.Substring(bstart + 15);
                    toolTipString = toolTipString.Substring(0, toolTipString.IndexOf("<"));
                    string[] keyValuePair = toolTipString.Split(new char[] { ' ' });
                    switch (keyValuePair[1])
                    {
                        case ("Hit"):
                            {
                                bonus_hit = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Strength"):
                            {
                                bonus_strength = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Stamina"):
                            {
                                bonus_stamina = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Dodge"):
                            {
                                bonus_dodge = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Parry"):
                            {
                                bonus_parry = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Defense"):
                            {
                                bonus_defense = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        default:
                            {
                                break;
                            }
                    }
                }

                /*int t7 = 0;
                int t8 = 0;
                if (name.IndexOf("Plagueheart") > 0)
                {
                    t7 = 1;
                }
                if (name.IndexOf("Deathbringer") > 0)
                {
                    t8 = 1;
                }*/

                string insertq = "INSERT INTO tankitems(wowheadID, name, ilvl, armor, hit, strength, stamina, dodge, parry, defense, red, yellow, blue, colourless, bonus_hit, bonus_strength, bonus_stamina, bonus_dodge, bonus_parry, bonus_defense, bodypart) VALUES('" + wowheadID + "', '" + name + "', '"+ilvl+"', '"+armor+"', '" + hit + "', '" + strength + "', '" + stamina + "', '" + dodge + "', '" + parry + "', '" + defense + "', '" + red + "', '" + yellow + "', '" + blue + "', '" + colourless + "', '" + bonus_hit + "', '" + bonus_strength + "', '" + bonus_stamina + "', '" + bonus_dodge + "', '" + bonus_parry + "', '" + bonus_defense + "', '" + bodypart + "')";
                //throw new Exception(insertq);
                db.InsertQuery(insertq);

            }
        }

        private void LoadIntoDB(string itemsURL)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");

            List<int> boots = new List<int>();
            string bootstring = GetHtml(itemsURL);
            while (bootstring.IndexOf("_[") > 0)
            {
                int start = bootstring.IndexOf("_[");
                bootstring = bootstring.Substring(start + 2);
                int end = bootstring.IndexOf("]");
                boots.Add(Int32.Parse(bootstring.Substring(0, end)));
            }

            for (int i = 0; i < boots.Count; i++)
            {
                string itemurl = "http://www.wowhead.com/?item=" + boots[i] + "&xml";
                string itemXml = GetHtml(itemurl);
                //now parse values from it
                XmlDoc itemDoc = new XmlDoc();
                itemDoc.LoadXml(itemXml);

                int wowheadID = boots[i];
                string name = itemDoc.GetNodeValue("//name").Replace("'", "");
                string bodypart = itemDoc.GetNodeValue("//inventorySlot");
                int hit = 0;
                int spellpower = 0;
                int haste = 0;
                int crit = 0;
                int spirit = 0;
                int intel = 0;
                int red = 0;
                int yellow = 0;
                int blue = 0;
                int colourless = 0;
                int bonus_spellpower = 0;
                int bonus_hit = 0;
                int bonus_haste = 0;
                int bonus_crit = 0;
                int bonus_spirit = 0;
                int bonus_int = 0;

                string dataValString = itemDoc.GetNodeValue("//jsonEquip");
                string[] dataVals = dataValString.Split(new char[] { ',' });
                for (int j = 0; j < dataVals.Length; j++)
                {
                    string[] keyValuePair = dataVals[j].Split(new char[] { ':' });
                    switch (keyValuePair[0])
                    {
                        case("critstrkrtng"):
                            {
                                crit = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("hitrtng"):
                            {
                                hit = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("splpwr"):
                            {
                                spellpower = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("int"):
                            {
                                intel = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("spi"):
                            {
                                spirit = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        case ("hastertng"):
                            {
                                haste = Int32.Parse(keyValuePair[1]);
                                break;
                            }
                        default:
                            {
                                break;
                            }
                    }
                }

                //now get socket bonus and sockets
                string toolTipString = itemDoc.GetNodeValue("//htmlTooltip");
                red = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Red Socket"));
                blue = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Blue Socket"));
                yellow = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Yellow Socket"));
                int meta = toolTipString.Select((c, k) => toolTipString.Substring(k)).Count(sub => sub.StartsWith("Meta Socket"));
                if (bodypart == "Waist") //belt buckle
                {
                    colourless++;
                }
                if (meta > 0) //assume ember skyflare. Ignore meta requirements.
                {
                    spellpower += 25;
                }
                int bstart = toolTipString.IndexOf("Socket Bonus: +");
                if (bstart > 0)
                {
                    toolTipString = toolTipString.Substring(bstart+15);
                    toolTipString = toolTipString.Substring(0, toolTipString.IndexOf("<"));
                    string[] keyValuePair = toolTipString.Split(new char[] { ' ' });
                    switch (keyValuePair[1])
                    {
                        case ("Haste"):
                            {
                                bonus_haste = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Critical"):
                            {
                                bonus_crit = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Spirit"):
                            {
                                bonus_spirit = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Spell"):
                            {
                                bonus_spellpower = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Intellect"):
                            {
                                bonus_int = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        case ("Hit"):
                            {
                                bonus_hit = Int32.Parse(keyValuePair[0]);
                                break;
                            }
                        default:
                            {
                                break;
                            }
                    }
                }

                int t7 = 0;
                int t8 = 0;
                if (name.IndexOf("Plagueheart") > 0)
                {
                    t7 = 1;
                }
                if (name.IndexOf("Deathbringer") > 0)
                {
                    t8 = 1;
                }
                
                string insertq = "INSERT INTO items(wowheadID, name, hit, spellpower, haste, crit, spirit, items.int, red, yellow, blue, colourless, bonus_spellpower, bonus_hit, bonus_haste, bonus_crit, bonus_spirit, bonus_int, bodypart, t7, t8) VALUES('" + wowheadID + "', '" + name + "', '" + hit + "', '" + spellpower + "', '" + haste + "', '" + crit + "', '" + spirit + "', '" + intel + "', '" + red + "', '" + yellow + "', '" + blue + "', '" + colourless + "', '" + bonus_spellpower + "', '" + bonus_hit + "', '" + bonus_haste + "', '" + bonus_crit + "', '" + bonus_spirit + "', '" + bonus_int + "', '" + bodypart + "', '"+t7+"', '"+t8+"')";
                db.InsertQuery(insertq);

            }
        }

        protected void ReloadTankItems(object sender, EventArgs e)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");

            //numbers last updated 18/5/09
            string chestURL = "http://www.wowhead.com/?items=4.4&filter=sl=5;minle=200;ub=6;cr=123;crs=3;crv=0"; //31
            string bootsURL = "http://www.wowhead.com/?items=4.4&filter=sl=8;minle=200;ub=6;cr=123;crs=3;crv=0"; //32
            string handsURL = "http://www.wowhead.com/?items=4.4&filter=sl=10;minle=200;ub=6;cr=123;crs=3;crv=0"; //32
            string headsURL = "http://www.wowhead.com/?items=4.4&filter=sl=1;minle=200;ub=6;cr=123;crs=3;crv=0"; //34
            string legsURL = "http://www.wowhead.com/?items=4.4&filter=sl=7;minle=200;ub=6;cr=123;crs=3;crv=0"; //35
            string shoulderURL = "http://www.wowhead.com/?items=4.4&filter=sl=3;minle=200;ub=6;cr=123;crs=3;crv=0"; //26
            string waistURL = "http://www.wowhead.com/?items=4.4&filter=sl=6;minle=200;ub=6;cr=123;crs=3;crv=0"; //29
            string wristURL = "http://www.wowhead.com/?items=4.4&filter=sl=9;minle=200;ub=6;cr=123;crs=3;crv=0"; //20
            string backURL = "http://www.wowhead.com/?items=4&filter=sl=16;minle=200;ub=6;cr=123;crs=3;crv=0"; //36
            string neckURL = "http://www.wowhead.com/?items=4&filter=sl=2;minle=200;minrl=80;ub=6;cr=123:114;crs=3:3;crv=0:0"; //38
            string ringsURL = "http://www.wowhead.com/?items=4&filter=sl=11;minle=200;minrl=80;ub=6;cr=123:114;crs=3:3;crv=0:0"; //46

            string weaponURL = "http://www.wowhead.com/?items=2&filter=sl=17;minle=200;ub=6;cr=125;crs=3;crv=0"; //45
            //string weapon2URL = "http://www.wowhead.com/?items=2&filter=sl=15;minle=200;ub=9"; //23
            //string offhandURL = "http://www.wowhead.com/?items&filter=sl=23;minle=200;ub=9"; //26
            

            db.Query("DELETE FROM tankitems");
            LoadIntoTankDB(bootsURL);
            LoadIntoTankDB(headsURL);
            LoadIntoTankDB(chestURL);
            LoadIntoTankDB(handsURL);
            LoadIntoTankDB(legsURL);
            LoadIntoTankDB(shoulderURL);
            LoadIntoTankDB(waistURL);
            LoadIntoTankDB(wristURL);
            LoadIntoTankDB(neckURL);
            LoadIntoTankDB(backURL);
            LoadIntoTankDB(weaponURL);
            //LoadIntoTankDB(weapon2URL);
            //LoadIntoTankDB(offhandURL);
            LoadIntoTankDB(ringsURL);               
        }

        protected void ReloadItems(object sender, EventArgs e)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");

            //numbers last updated 20/4/09
            string bootsURL = "http://www.wowhead.com/?items=4.1&filter=sl=8;minle=200;ub=9"; //29
            string headsURL = "http://www.wowhead.com/?items=4.1&filter=sl=1;minle=200;ub=9"; //27
            string chestURL = "http://www.wowhead.com/?items=4.1&filter=sl=5;minle=200;ub=9"; //33
            string handsURL = "http://www.wowhead.com/?items=4.1&filter=sl=10;minle=200;ub=9"; //32
            string legsURL = "http://www.wowhead.com/?items=4.1&filter=sl=7;minle=200;ub=9"; //27
            string shoulderURL = "http://www.wowhead.com/?items=4.1&filter=sl=3;minle=200;ub=9"; //24
            string waistURL = "http://www.wowhead.com/?items=4.1&filter=sl=6;minle=200;ub=9"; //36
            string wristURL = "http://www.wowhead.com/?items=4.1&filter=sl=9;minle=200;ub=9"; //27
            string neckURL = "http://www.wowhead.com/?items=4&filter=sl=2;minle=200;ub=9;cr=123;crs=1;crv=50"; //43
            string backURL = "http://www.wowhead.com/?items=4&filter=sl=16;minle=200;ub=9;cr=123;crs=1;crv=0"; //40
            string weaponURL = "http://www.wowhead.com/?items=2&filter=sl=21;minle=200;ub=9"; //22
            string weapon2URL = "http://www.wowhead.com/?items=2&filter=sl=15;minle=200;ub=9"; //23
            string offhandURL = "http://www.wowhead.com/?items&filter=sl=23;minle=200;ub=9"; //26
            string ringsURL = "http://www.wowhead.com/?items&filter=sl=11;minle=200;ub=9;cr=123;crs=1;crv=0"; //40

            db.Query("DELETE FROM items");
            LoadIntoDB(bootsURL); 
            LoadIntoDB(headsURL); 
            LoadIntoDB(chestURL); 
            LoadIntoDB(handsURL); 
            LoadIntoDB(legsURL); 
            LoadIntoDB(shoulderURL);
            LoadIntoDB(waistURL); 
            LoadIntoDB(wristURL); 
            LoadIntoDB(neckURL); 
            LoadIntoDB(backURL); 
            LoadIntoDB(weaponURL); 
            LoadIntoDB(weapon2URL); 
            LoadIntoDB(offhandURL); 
            LoadIntoDB(ringsURL);

            //insert boots & Gloves enchants manually
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart) VALUES('Enchant Boots - Icewalker', '12', '0', '0', '12', '0', '0', 'Boot Enchant')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart) VALUES('Enchant Boots - Greater Spirit', '0', '0', '0', '0', '18', '0', 'Boot Enchant')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart) VALUES('Enchant Gloves - Precision', '20', '0', '0', '0', '0', '0', 'Gloves Enchant')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart) VALUES('Enchant Gloves - Exceptional Spell Power', '0', '28', '0', '0', '0', '0', 'Gloves Enchant')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart) VALUES('Enchant Weapon - Accuracy', '25', '0', '0', '25', '0', '0', 'Weapon Enchant')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart) VALUES('Enchant Weapon - Superior Spell Power', '0', '63', '0', '0', '0', '0', 'Weapon Enchant')");

            //and trinkets
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Dying Curse', '71', '141.5', '0', '0', '0', '0', 'Trinket', '40255')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Flare of the Heavens', '0', '157.25', '0', '120', '0', '0', 'Trinket', '45518')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Illustration of the Dragon Soul', '0', '200', '0', '0', '0', '0', 'Trinket', '40432')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Living Flame', '107', '91.7', '0', '0', '0', '0', 'Trinket', '45148')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Elemental Focus Stone', '108', '96.6', '0', '0', '0', '0', 'Trinket', '45866')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Eye of Broodmother', '0', '125', '0', '87', '0', '0', 'Trinket', '45308')");
            db.Query("INSERT INTO items(name, hit, spellpower, haste, crit, spirit, items.int, bodypart, wowheadID) VALUES('Scale of Fates', '0', '125', '72', '0', '0', '0', 'Trinket', '45466')");      
        }

        protected void Page_Load(object sender, EventArgs e)
        {
        }

        public void ShowRankedTankGearList(object sender, EventArgs e)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");
            int health = 29000;
            int armor = 26000;
            int defense = 606;
            //int strength = 920;
            int parry = 300;
            int dodge = 180;
            double parrycap = 47;
            double dodgecap = 88.128;
            double misscap = 16;
            double drconst = 0.956;
            double parryPerPoint = 49.185;
            double dodgePerPoint = 39.348;
            double defensePerPoint = 4.9185;
            db.Query("UPDATE tankitems SET effective_health=( ("+health+"+10*(stamina+24*blue+24*yellow+24*red))/(1-(("+armor+"+armor)/(("+armor+"+armor)+16635))) - ("+health+"/(1-("+armor+"/("+armor+"+16635)))) )");
            db.Query("UPDATE tankitems SET parry_percentage=( 1/(1/" + parrycap + " + " + drconst + "/((" + parry + "+parry+strength/4)/" + parryPerPoint + "+0.04*(defense+" + defense + ")/" + defensePerPoint + ")) - 1/(1/" + parrycap + " + " + drconst + "/( (" + parry + "/" + parryPerPoint + ")+0.04*" + defense + "/" + defensePerPoint + ")) )");
            db.Query("UPDATE tankitems SET dodge_percentage=( 1/(1/" + dodgecap + " + " + drconst + "/((" + dodge + "+dodge)/" + dodgePerPoint + "+0.04*(defense+" + defense + ")/" + defensePerPoint + ")) - 1/(1/" + dodgecap + " + " + drconst + "/( (" + dodge + "/" + dodgePerPoint + ")+0.04*" + defense + "/" + defensePerPoint + ")) )");
            db.Query("UPDATE tankitems SET miss_percentage=( 1/(1/" + misscap + " + " + drconst + "/((defense+" + defense + ")/" + defensePerPoint + ")) - 1/(1/" + misscap + " + " + drconst + "/(" + defense + "/" + defensePerPoint + ")) )");   
            db.Query("UPDATE tankitems SET survival_value = effective_health");
            db.Query("UPDATE tankitems SET survival_value = survival_value + 2*((" + health + "+10*(stamina+24*blue+24*yellow+24*red))/(1-(" + armor + "/(" + armor + "+16635))))*(parry_percentage+dodge_percentage+miss_percentage)/100");
            
            //now to display them
            //FEET
            XmlNodeList feet = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Feet' ORDER BY survival_value DESC").SelectNodes("//row");
            List<WowItem> items = new List<WowItem>();
            for (int i = 0; i < feet.Count; i++)
            {
                WowItem item = new WowItem(feet[i]);
                items.Add(item);
            }
            feetList.DataSource = items;
            feetList.DataBind();

            //HEADS
            XmlNodeList heads = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Head' ORDER BY survival_value DESC").SelectNodes("//row");
            List<WowItem> headItems = new List<WowItem>();
            for (int i = 0; i < heads.Count; i++)
            {
                WowItem item = new WowItem(heads[i]);
                headItems.Add(item);
            }
            headList.DataSource = headItems;
            headList.DataBind();

            //CHEST
            XmlNodeList chests = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Chest' ORDER BY survival_value DESC").SelectNodes("//row");
            List<WowItem> chestItems = new List<WowItem>();
            for (int i = 0; i < chests.Count; i++)
            {
                WowItem item = new WowItem(chests[i]);
                chestItems.Add(item);
            }
            chestList.DataSource = chestItems;
            chestList.DataBind();

            //HANDS
            XmlNodeList xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Hands' ORDER BY survival_value DESC").SelectNodes("//row");
            List<WowItem> xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            handsList.DataSource = xItems;
            handsList.DataBind();

            //LEGS
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Legs' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            legsList.DataSource = xItems;
            legsList.DataBind();

            //SHOULDER
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Shoulder' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            shoulderList.DataSource = xItems;
            shoulderList.DataBind();

            //WAIST
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Waist' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            waistList.DataSource = xItems;
            waistList.DataBind();

            //WRIST
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Wrist' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            wristList.DataSource = xItems;
            wristList.DataBind();

            //NECK
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Neck' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            neckList.DataSource = xItems;
            neckList.DataBind();

            //BACK
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Back' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            backList.DataSource = xItems;
            backList.DataBind();

           
            //WEAPON
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Two-Hand' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            mainhandList.DataSource = xItems;
            mainhandList.DataBind();
            /*
            //WEAPON2
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Ranged' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            rangedList.DataSource = xItems;
            rangedList.DataBind();

            //OFFHAND
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Held In Off-hand' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            offhandList.DataSource = xItems;
            offhandList.DataBind();
            */
            //RINGS
            xList = db.Query("SELECT *, survival_value as weight FROM tankitems WHERE bodypart='Finger' ORDER BY survival_value DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            fingerList.DataSource = xItems;
            fingerList.DataBind();//*/
        }

        public void ShowRankedGearList(object sender, EventArgs e)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");
            db.Query("delete from ranked_items");
            db.Query("insert into ranked_items select *, 0 as weight FROM items");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, red=red-1 WHERE red > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, red=red-1 WHERE red > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, blue=blue-1 WHERE blue > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, blue=blue-1 WHERE blue > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, yellow=yellow-1 WHERE yellow > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, yellow=yellow-1 WHERE yellow > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, colourless=colourless-1 WHERE colourless > 0");
            db.Query("UPDATE ranked_items SET spellpower = spellpower+19, colourless=colourless-1 WHERE colourless > 0");
            db.Query("UPDATE ranked_items SET weight=hit*"+spellhitWeight.Text+"+spellpower*" + spellpowerWeight.Text + "+haste*" + hasteWeight.Text + "+crit*"+critWeight.Text+"+spirit*"+spiritWeight.Text+"+ranked_items.int*"+intWeight.Text);

            //now to display them
            //FEET
            XmlNodeList feet = db.Query("SELECT * FROM ranked_items WHERE bodypart='Feet' ORDER BY weight DESC").SelectNodes("//row");
            List<WowItem>items = new List<WowItem>();
            for (int i = 0; i < feet.Count; i++)
            {
                WowItem item = new WowItem(feet[i]);
                items.Add(item);
            }
            feetList.DataSource = items;
            feetList.DataBind();

            //HEADS
            XmlNodeList heads = db.Query("SELECT * FROM ranked_items WHERE bodypart='Head' ORDER BY weight DESC").SelectNodes("//row");
            List<WowItem> headItems = new List<WowItem>();
            for (int i = 0; i < heads.Count; i++)
            {
                WowItem item = new WowItem(heads[i]);
                headItems.Add(item);
            }
            headList.DataSource = headItems;
            headList.DataBind();

            //CHEST
            XmlNodeList chests = db.Query("SELECT * FROM ranked_items WHERE bodypart='Chest' ORDER BY weight DESC").SelectNodes("//row");
            List<WowItem> chestItems = new List<WowItem>();
            for (int i = 0; i < chests.Count; i++)
            {
                WowItem item = new WowItem(chests[i]);
                chestItems.Add(item);
            }
            chestList.DataSource = chestItems;
            chestList.DataBind();

            //HANDS
            XmlNodeList xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Hands' ORDER BY weight DESC").SelectNodes("//row");
            List<WowItem> xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            handsList.DataSource = xItems;
            handsList.DataBind();
            
            //LEGS
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Legs' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            legsList.DataSource = xItems;
            legsList.DataBind();

            //SHOULDER
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Shoulder' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            shoulderList.DataSource = xItems;
            shoulderList.DataBind();

            //WAIST
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Waist' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            waistList.DataSource = xItems;
            waistList.DataBind();

            //WRIST
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Wrist' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            wristList.DataSource = xItems;
            wristList.DataBind();
            
            //NECK
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Neck' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            neckList.DataSource = xItems;
            neckList.DataBind();

            //BACK
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Back' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            backList.DataSource = xItems;
            backList.DataBind();

            //WEAPON
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Main Hand' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            mainhandList.DataSource = xItems;
            mainhandList.DataBind();
            
            //WEAPON2
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Ranged' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            rangedList.DataSource = xItems;
            rangedList.DataBind();
            
            //OFFHAND
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Held In Off-hand' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            offhandList.DataSource = xItems;
            offhandList.DataBind();
            
            //RINGS
            xList = db.Query("SELECT * FROM ranked_items WHERE bodypart='Finger' ORDER BY weight DESC").SelectNodes("//row");
            xItems = new List<WowItem>();
            for (int i = 0; i < xList.Count; i++)
            {
                WowItem item = new WowItem(xList[i]);
                xItems.Add(item);
            }
            fingerList.DataSource = xItems;
            fingerList.DataBind();//*/
        }

        public void ShowGearset(object sender, EventArgs e)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");
            int hitTarget = Int32.Parse(hitValTarget.Text);
            
            int t8 = 0;
            if (t8bonus4pc.Checked)
            {
                t8 = 4;
            }
            else if (t8bonus2pc.Checked)
            {
                t8 = 2;
            }
            XmlDocument gearDoc = db.Query("SELECT *, weight-If(hit<?, ?-hit, 0)*? as dps FROM gearset WHERE t8>=?  order by dps desc LIMIT 1", new string[] { hitTarget.ToString(), hitTarget.ToString(), hitValue.Text, t8.ToString() });
            
            //have gearset, need to generate display string
            gearsetLiteral.Text = getItemHtml(gearDoc.SelectSingleNode("//head").InnerText, "Head");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//neck").InnerText, "Neck");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//shoulder").InnerText, "Shoulder");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//back").InnerText, "Back");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//chest").InnerText, "Chest");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//wrist").InnerText, "Wrist");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//hands").InnerText, "Hands");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//waist").InnerText, "Waist");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//legs").InnerText, "Legs");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//feet").InnerText, "Feet");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//ring1").InnerText, "Ring 1");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//ring2").InnerText, "Ring 2");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//trinket1").InnerText, "Trinket 1");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//trinket2").InnerText, "Trinket 2");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//mainhand").InnerText, "Mainhand");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//offhand").InnerText, "Offhand");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//ranged").InnerText, "Ranged");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//bootenchant").InnerText, "Boot Enchant");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//glovesenchant").InnerText, "Gloves Enchant");
            gearsetLiteral.Text += getItemHtml(gearDoc.SelectSingleNode("//weaponenchant").InnerText, "Weapon Enchant");

            gearsetLiteral.Text += "<br>Total hit: <strong>"+gearDoc.SelectSingleNode("//hit").InnerText+"</strong>";
            gearsetLiteral.Text += "<br>Total weight: <strong>" + gearDoc.SelectSingleNode("//dps").InnerText + "</strong> (not inc T8 bonus)";
        }

        public string getItemHtml(string itemID, string itemName)
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");
            XmlDocument item = db.Query("SELECT * FROM combined_items WHERE id='" + itemID + "'");
            if (!ejformat.Checked)
            {
                return itemName + ": <a href='http://www.wowhead.com?item=" + item.SelectSingleNode("//wowheadID").InnerText + "'>" + item.SelectSingleNode("//name").InnerText + "</a><br />";
            }
            else
            {
                return itemName + " - [URL='http://www.wowhead.com/?item=" + item.SelectSingleNode("//wowheadID").InnerText + "']" + item.SelectSingleNode("//name").InnerText + "[/URL]";
            }
        }

        public void GenerateOptimalGearset(object sender, EventArgs e)
        {
            bool blacksmith = false;
            bool jewelcrafter = false;

            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");
            db.Open();
            //STEP 1 - create combined table with gemmed items in.
           
            db.Query("delete from combined_items", false);
            db.Query("insert into combined_items(name, hit, spellpower, haste, crit, spirit, combined_items.int, red, yellow, blue, bonus_spellpower, bonus_haste, bonus_crit, bonus_spirit, bonus_int, bodypart, colourless, wowheadid, t7, t8, weight, maxweight, decount) select name, hit, spellpower, haste, crit, spirit, items.int, red, yellow, blue, bonus_spellpower, bonus_haste, bonus_crit, bonus_spirit, bonus_int, bodypart, colourless, wowheadid, t7, t8, 1000.0001 as weight, 1000.0001 as maxweight, 0 as decount from items", false);
            if (blacksmith) //wrist and hands have extra colourless socket
            {
                db.Query("UPDATE combined_items SET colourless=1 WHERE bodypart='Wrist' OR bodypart='Hands'", false);
            }
            XmlNodeList gems;
            if (jewelcrafter)
            {
                gems = db.Query("SELECT * from gems", false).SelectNodes("//row");
            }
            else
            {
                gems = db.Query("SELECT * from gems WHERE name NOT LIKE '%Dragons Eye%'", false).SelectNodes("//row");
            }
            
            string[] gemcolors = new string[] { "red", "blue", "yellow", "colourless" };

            for (int c = 0; c < gemcolors.Length; c++)
            {
                XmlNodeList itemsToGem = db.Query("SELECT * from combined_items WHERE " + gemcolors[c] + ">0", false).SelectNodes("//row");
                //throw new Exception("SELECT * from combined_items WHERE " + gemcolors[c] + ">0-->"+itemsToGem.Count);

                while (itemsToGem.Count > 0)
                {
                    itemsToGem = db.Query("SELECT * from combined_items WHERE "+gemcolors[c]+">0", false).SelectNodes("//row");
                    for (int i = 0; i < itemsToGem.Count; i++)
                    {
                        XmlDoc itemToGem = new XmlDoc();
                        itemToGem.LoadXml(itemsToGem[i].OuterXml);

                        string basename = itemToGem.GetNodeValue("//name");
                        string bodypart = itemToGem.GetNodeValue("//bodypart");
                        string weight = itemToGem.GetNodeValue("//weight");
                        string wowheadID = itemToGem.GetNodeValue("//wowheadID");
                        string maxweight = itemToGem.GetNodeValue("//maxweight");
                        int basehit = Int32.Parse(itemToGem.GetNodeValue("//hit"));
                        int basespellpower = Int32.Parse(itemToGem.GetNodeValue("//spellpower"));
                        int basehaste = Int32.Parse(itemToGem.GetNodeValue("//haste"));
                        int crit = Int32.Parse(itemToGem.GetNodeValue("//crit")); //no gems with crit
                        int basespirit = Int32.Parse(itemToGem.GetNodeValue("//spirit"));
                        int intel = Int32.Parse(itemToGem.GetNodeValue("//int")); //no gems with int
                        int basered = Int32.Parse(itemToGem.GetNodeValue("//red"));
                        int baseyellow = Int32.Parse(itemToGem.GetNodeValue("//yellow"));
                        int baseblue = Int32.Parse(itemToGem.GetNodeValue("//blue"));
                        int basecolourless = Int32.Parse(itemToGem.GetNodeValue("//colourless"));
                        int basedecount = Int32.Parse(itemToGem.GetNodeValue("//decount"));
                        int t7 = Int32.Parse(itemToGem.GetNodeValue("//t7"));
                        int t8 = Int32.Parse(itemToGem.GetNodeValue("//t8"));

                        for (int j = 0; j < gems.Count; j++)
                        {
                            XmlDoc gem = new XmlDoc();
                            gem.LoadXml(gems[j].OuterXml);

                            string name = basename + "+" + gem.GetNodeValue("//name");
                            int hit = basehit + Int32.Parse(gem.GetNodeValue("//hit"));
                            int spellpower = basespellpower+Int32.Parse(gem.GetNodeValue("//spellpower"));
                            int haste = basehaste+Int32.Parse(gem.GetNodeValue("//haste"));
                            int spirit = basespirit+Int32.Parse(gem.GetNodeValue("//spirit"));
                            int red=basered;
                            int blue=baseblue;
                            int yellow=baseyellow;
                            int colourless =basecolourless;
                            int decount = basedecount;
                            
                            if (gemcolors[c] == "red")
                                red--;
                            else if (gemcolors[c] == "blue")
                                blue--;
                            else if (gemcolors[c] == "yellow")
                                yellow--;
                            else if (gemcolors[c] == "colourless")
                                colourless--;
                            else
                                throw new Exception("Invalid colour ["+gemcolors[c]+"]");

                            int bonus_spellpower = 0;
                            int bonus_hit = 0;
                            int bonus_haste = 0;
                            int bonus_crit = 0;
                            int bonus_spirit = 0;
                            int bonus_int = 0;
                            if (gem.GetNodeValue("//"+gemcolors[c]) == "1" || gemcolors[c] == "colourless")
                            {
                                //keep socket bonus
                                bonus_spellpower = Int32.Parse(itemToGem.GetNodeValue("//bonus_spellpower"));
                                bonus_hit = Int32.Parse(itemToGem.GetNodeValue("//bonus_hit"));
                                bonus_haste = Int32.Parse(itemToGem.GetNodeValue("//bonus_haste"));
                                bonus_crit = Int32.Parse(itemToGem.GetNodeValue("//bonus_crit"));
                                bonus_spirit = Int32.Parse(itemToGem.GetNodeValue("//bonus_spirit"));
                                bonus_int = Int32.Parse(itemToGem.GetNodeValue("//bonus_int"));
                            }
                            if (gem.GetNodeValue("//name").IndexOf("Dragons Eye") > 0)
                            {
                                decount++;
                            }
                            string insertq = "INSERT INTO combined_items(wowheadID, name, hit, spellpower, haste, crit, spirit, combined_items.int, red, yellow, blue, colourless, bonus_spellpower, bonus_hit, bonus_haste, bonus_crit, bonus_spirit, bonus_int, bodypart, weight, maxweight, decount, t7, t8) VALUES('"+wowheadID+"', '" + name + "', '" + hit + "', '" + spellpower + "', '" + haste + "', '" + crit + "', '" + spirit + "', '" + intel + "', '" + red + "', '" + yellow + "', '" + blue + "', '"+colourless+"', '" + bonus_spellpower + "', '" + bonus_hit + "', '" + bonus_haste + "', '" + bonus_crit + "', '" + bonus_spirit + "', '" + bonus_int + "', '" + bodypart + "', '" + weight + "', '"+maxweight+"', '"+decount+"', '"+t7+"', '"+t8+"')";
                            db.InsertQuery(insertq, false, false);
                            //throw new Exception(insertq);
                            //Logger.Log(insertq);
                        }
                        db.Query("DELETE FROM combined_items WHERE id='" + itemToGem.GetNodeValue("//id") + "'", false);

                    } //end itemsToGem
                } //endwhile
            } //end gemcolours

            //now apply weighting
            double hasteweight = Double.Parse(hasteWeight.Text);
            double spellpowerweight = Double.Parse(spellpowerWeight.Text);
            double critweight = Double.Parse(critWeight.Text);
            double spiritweight = Double.Parse(spiritWeight.Text);
            double intweight = Double.Parse(intWeight.Text);
            double hitweight = Double.Parse(spellhitWeight.Text); //hit can never be worth more than this... used for 'maxweight' calculation to assist pruning.
            //do set to an overly 'high' value or else you risk missing combinations where it is better to take hit on a certain
            //slot even though it is a worse item
            //double t7maxweight = Int32.Parse(t7Weight.Text);
            //double t8maxweight = Int32.Parse(t8Weight.Text);
            db.Query("UPDATE combined_items SET weight=spellpower*" + spellpowerweight + "+haste*" + hasteweight + "+crit*" + critweight + "+spirit*" + spiritweight + "+combined_items.int*" + intweight, false);
            db.Query("UPDATE combined_items SET weight=weight+bonus_spellpower*" + spellpowerweight + "+bonus_haste*" + hasteweight + "+bonus_crit*" + critweight + "+bonus_spirit*" + spiritweight + "+bonus_int*" + intweight, false);
            db.Query("UPDATE combined_items SET maxweight=weight+hit*" + hitweight + "+bonus_hit*" + hitweight, false);
 

            //prune all but top 1 from each set, except 2 from rings & trinkets
            XmlNodeList groups = db.Query("SELECT *, count(*) as count FROM `TrippWS`.`combined_items` GROUP BY bodypart, hit, decount, t7, t8 order by bodypart", false).SelectNodes("//row");
            for (int i = 0; i < groups.Count; i++)
            {
                XmlDoc group = new XmlDoc();
                group.LoadXml(groups[i].OuterXml);
                int limit;
                if (group.GetNodeValue("//bodypart") == "Finger" || group.GetNodeValue("//bodypart") == "Trinket")
                {
                    limit = 2;
                }
                else
                {
                    limit = 1;
                }
                //mysql doesn't support subquery, so do it manually.
                //Cannot have Dragon's Eye cause non DE items to be pruned.
                XmlNodeList weights = db.Query("SELECT weight FROM combined_items WHERE decount='" + group.GetNodeValue("//decount") + "' AND t7='" + group.GetNodeValue("//t7") + "' AND t8='" + group.GetNodeValue("//t8") + "' AND bodypart='" + group.GetNodeValue("//bodypart") + "' AND hit=" + group.GetNodeValue("//hit") + " ORDER BY weight DESC LIMIT " + limit, false).SelectNodes("//row");
                string andClause = "";
                for (int j = 0; j < weights.Count; j++)
                {
                    XmlDoc temp = new XmlDoc();
                    temp.LoadXml(weights[j].OuterXml);
                    andClause += "AND weight<("+temp.GetNodeValue("//weight")+"-0.1)";
                }
                //if an item has =/less hit, less weight, =/more dragon's eyes, =/less t7, and =/less t8, it is safe to remove
                if (group.GetNodeValue("//bodypart") != "Trinket" || group.GetNodeValue("//hit") == "0") //don't delete any trinkets with +hit
                    db.Query("DELETE FROM combined_items WHERE decount >='" + group.GetNodeValue("//decount") + "' AND bodypart='" + group.GetNodeValue("//bodypart") + "' AND t7<=" + group.GetNodeValue("//t7") + " AND t8<=" + group.GetNodeValue("//t8") + " AND hit<=" + group.GetNodeValue("//hit") + " " + andClause, false);

                //or it can have more t7 but not enough to make up value
                andClause = "";
                for (int j = 0; j < weights.Count; j++)
                {
                    XmlDoc temp = new XmlDoc();
                    temp.LoadXml(weights[j].OuterXml);
                    andClause += "AND weight<(" + temp.GetNodeValue("//weight") + "-" + t7Weight.Text + "-0.1)";
                }
                db.Query("DELETE FROM combined_items WHERE decount >='" + group.GetNodeValue("//decount") + "' AND bodypart='" + group.GetNodeValue("//bodypart") + "' AND t7>=" + group.GetNodeValue("//t7") + " AND t8<=" + group.GetNodeValue("//t8") + " AND hit<=" + group.GetNodeValue("//hit") + " " + andClause, false);
 
                //or it can have more t8 but not enough to make up value
                andClause = "";
                for (int j = 0; j < weights.Count; j++)
                {
                    XmlDoc temp = new XmlDoc();
                    temp.LoadXml(weights[j].OuterXml);
                    andClause += "AND weight<(" + temp.GetNodeValue("//weight") + "-"+t8Weight.Text + "-0.1)";
                }
                db.Query("DELETE FROM combined_items WHERE decount >='" + group.GetNodeValue("//decount") + "' AND bodypart='" + group.GetNodeValue("//bodypart") + "' AND t7<=" + group.GetNodeValue("//t7") + " AND t8>=" + group.GetNodeValue("//t8") + " AND hit<=" + group.GetNodeValue("//hit") + " " + andClause, false);
                
                //throw new Exception("DELETE FROM combined_items WHERE bodypart='" + group.GetNodeValue("//bodypart") + "' AND hit<=" + group.GetNodeValue("//hit") + " " + andClause);
            }
            //may still need to do the 'maxweight' thing. But this alone won't.

            /*
            //loop and nuke extras (is there a better way to do this?
            XmlNodeList items = db.Query("SELECT * FROM combined_items order by weight desc").SelectNodes("//row");
            for (int i = 0; i < items.Count; i++)
            {
                XmlDoc item = new XmlDoc();
                item.LoadXml(items[i].OuterXml);
                //do weight-0.1, since 'less than' causes problems with floats
                Thread.Sleep(250);

                //EXCEPTION with rings, you must keep the top two of each hit score
                //so for rings, delete everything below it IF there is also 1+ higher than it
                if (item.GetNodeValue("//bodypart")!="Finger" || db.Query("SELECT * FROM combined_items WHERE bodypart='" + item.GetNodeValue("//bodypart") + "' AND (bonus_hit+hit)>=" + item.GetNodeValue("//hit") + "+" + item.GetNodeValue("//bonus_hit") + " AND weight>(" + item.GetNodeValue("//weight") + "+0.1)").SelectNodes("//row").Count > 0)
                {
                    //delete any items in the same category with less hit but equal weight
                    string q1 = "DELETE FROM combined_items WHERE bodypart='" + item.GetNodeValue("//bodypart") + "' AND (bonus_hit+hit)<=" + item.GetNodeValue("//hit") + "+"+item.GetNodeValue("//bonus_hit")+ " AND weight<(" + item.GetNodeValue("//weight") + "-0.1)";
                    db.Query(q1);
                }
                if (item.GetNodeValue("//bodypart") != "Finger")
                {                
                    //delete any items in the same category with 'maxweight' lower than this item's maxweight but MORE hit
                    db.Query("DELETE FROM combined_items WHERE bodypart='" + item.GetNodeValue("//bodypart") + "' AND (bonus_hit+hit)>=" + item.GetNodeValue("//hit") + "+"+item.GetNodeValue("//bonus_hit")+ " AND maxweight<(" + item.GetNodeValue("//maxweight") + "-0.1)");
                }
            }
            */

            //now do the gear sets with the remaining items
            //very large dataset... needs pruning at each stage to reduce combinations

            //will this give too large a dataset to perform the operation on?
            db.Query("DROP table if exists gearset", false);
            db.Query("CREATE TABLE gearset as SELECT chest.id as chest, waist.id as waist, chest.decount+waist.decount as decount, chest.hit+waist.hit+chest.bonus_hit+waist.bonus_hit as hit, chest.t7+waist.t7 as t7, chest.t8+waist.t8 as t8, waist.weight+chest.weight as weight FROM combined_items chest LEFT JOIN combined_items waist ON waist.bodypart='Waist' AND chest.bodypart='Chest' WHERE chest.bodypart='Chest' order by weight desc, hit desc", false);
            //combine 1st four sets, then PRUNE!
            PruneGearsetTable();

            //feet
            db.Query("DROP TABLE if exists gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT feet.id as feet, gearset.chest, gearset.waist, gearset.decount+feet.decount as decount, gearset.hit+feet.hit+feet.bonus_hit as hit, gearset.t7+feet.t7 as t7, gearset.t8+feet.t8 as t8, gearset.weight+feet.weight as weight from gearset LEFT JOIN combined_items feet ON feet.bodypart='Feet'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //head
            db.Query("DROP TABLE if exists gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT head.id as head, gearset.chest, gearset.waist, gearset.feet, gearset.decount+head.decount as decount, gearset.hit+head.hit+head.bonus_hit as hit, gearset.t7+head.t7 as t7, gearset.t8+head.t8 as t8, gearset.weight+head.weight as weight from gearset LEFT JOIN combined_items head ON head.bodypart='Head'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //hands
            db.Query("DROP TABLE if exists gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.head, gearset.chest, hands.id as hands, gearset.waist, gearset.feet, gearset.decount+hands.decount as decount, gearset.hit+hands.hit+hands.bonus_hit as hit, gearset.t7+hands.t7 as t7, gearset.t8+hands.t8 as t8, gearset.weight+hands.weight as weight from gearset LEFT JOIN combined_items hands ON hands.bodypart='Hands'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //shoulder
            db.Query("DROP TABLE if exists gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.head, gearset.hands, gearset.chest, shoulder.id as shoulder, gearset.waist, gearset.feet, gearset.decount+shoulder.decount as decount, gearset.hit+shoulder.hit+shoulder.bonus_hit as hit, gearset.t7+shoulder.t7 as t7, gearset.t8+shoulder.t8 as t8, gearset.weight+shoulder.weight as weight from gearset LEFT JOIN combined_items shoulder ON shoulder.bodypart='Shoulder'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2",false);
            PruneGearsetTable();

            //legs
            db.Query("DROP TABLE if exists gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.shoulder, gearset.head, gearset.hands, gearset.chest, legs.id as legs, gearset.waist, gearset.feet, gearset.decount+legs.decount as decount, gearset.hit+legs.hit+legs.bonus_hit as hit, gearset.t7+legs.t7 as t7, gearset.t8+legs.t8 as t8, gearset.weight+legs.weight as weight from gearset LEFT JOIN combined_items legs ON legs.bodypart='Legs'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //wrist
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, wrist.id as wrist, gearset.waist, gearset.feet, gearset.decount+wrist.decount as decount, gearset.hit+wrist.hit+wrist.bonus_hit as hit, gearset.t7+wrist.t7 as t7, gearset.t8+wrist.t8 as t8, gearset.weight+wrist.weight as weight from gearset LEFT JOIN combined_items wrist ON wrist.bodypart='Wrist'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //neck
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, neck.id as neck, gearset.waist, gearset.feet, gearset.decount+neck.decount as decount, gearset.hit+neck.hit+neck.bonus_hit as hit, gearset.t7+neck.t7 as t7, gearset.t8+neck.t8 as t8, gearset.weight+neck.weight as weight from gearset LEFT JOIN combined_items neck ON neck.bodypart='Neck'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //back
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, back.id as back, gearset.waist, gearset.feet, gearset.decount+back.decount as decount, gearset.hit+back.hit+back.bonus_hit as hit, gearset.t7+back.t7 as t7, gearset.t8+back.t8 as t8, gearset.weight+back.weight as weight from gearset LEFT JOIN combined_items back ON back.bodypart='Back'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();
            
            //weapon
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, mainhand.id as mainhand, gearset.waist, gearset.feet, gearset.decount+mainhand.decount as decount, gearset.hit+mainhand.hit+mainhand.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+mainhand.weight as weight from gearset LEFT JOIN combined_items mainhand ON mainhand.bodypart='Main Hand'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //offhand
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, offhand.id as offhand, gearset.waist, gearset.feet, gearset.decount+offhand.decount as decount, gearset.hit+offhand.hit+offhand.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+offhand.weight as weight from gearset LEFT JOIN combined_items offhand ON offhand.bodypart='Held In Off-Hand'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();
            
            //ranged
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, ranged.id as ranged, gearset.waist, gearset.feet, gearset.decount+ranged.decount as decount, gearset.hit+ranged.hit+ranged.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+ranged.weight as weight from gearset LEFT JOIN combined_items ranged ON ranged.bodypart='Ranged'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //ring1
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, ring.id as ring1, gearset.waist, gearset.feet, gearset.decount+ring.decount as decount, gearset.hit+ring.hit+ring.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+ring.weight as weight from gearset LEFT JOIN combined_items ring ON ring.bodypart='Finger'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //ring2
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, gearset.ring1, ring.id as ring2, gearset.waist, gearset.feet, gearset.decount+ring.decount as decount, gearset.hit+ring.hit+ring.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+ring.weight as weight from gearset LEFT JOIN combined_items ring ON ring.bodypart='Finger'", false);

            //must make sure we dont' have the same ring twice
            db.Query("DROP TABLE IF EXISTS tempring", false);
            db.Query("CREATE TABLE tempring AS SELECT gearset2.*, r1.wowheadID as r1ID, r2.wowheadID as r2ID FROM gearset2 LEFT JOIN combined_items r1 ON r1.id=gearset2.ring1 LEFT JOIN combined_items r2 ON r2.id = gearset2.ring2", false);
            db.Query("DELETE FROM tempring WHERE r1ID=r2ID", false);
            db.Query("ALTER TABLE tempring DROP COLUMN r1ID", false);
            db.Query("ALTER TABLE tempring DROP COLUMN r2ID", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM tempring", false);
            db.Query("DROP TABLE tempring", false);
            PruneGearsetTable();

            //boot enchant
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, gearset.ring1, gearset.ring2, bootenchant.id as bootenchant, gearset.waist, gearset.feet, gearset.decount, gearset.hit+bootenchant.hit+bootenchant.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+bootenchant.weight as weight from gearset LEFT JOIN combined_items bootenchant ON bootenchant.bodypart='Boot Enchant'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //gloves enchant
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, gearset.ring1, gearset.ring2, gearset.bootenchant, glovesenchant.id as glovesenchant, gearset.waist, gearset.feet, gearset.decount, gearset.hit+glovesenchant.hit+glovesenchant.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+glovesenchant.weight as weight from gearset LEFT JOIN combined_items glovesenchant ON glovesenchant.bodypart='Gloves Enchant'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //weapons enchant
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, gearset.ring1, gearset.ring2, gearset.bootenchant, gearset.glovesenchant, weaponenchant.id as weaponenchant, gearset.waist, gearset.feet, gearset.decount, gearset.hit+weaponenchant.hit+weaponenchant.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+weaponenchant.weight as weight from gearset LEFT JOIN combined_items weaponenchant ON weaponenchant.bodypart='Weapon Enchant'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //trinket 1
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, gearset.ring1, gearset.ring2, gearset.bootenchant, gearset.glovesenchant, gearset.weaponenchant, trinket1.id as trinket1, gearset.waist, gearset.feet, gearset.decount, gearset.hit+trinket1.hit+trinket1.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+trinket1.weight as weight from gearset LEFT JOIN combined_items trinket1 ON trinket1.bodypart='Trinket'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            PruneGearsetTable();

            //trinket 2 (+delete any sets with same trinket twice)
            db.Query("DROP TABLE gearset2", false);
            db.Query("CREATE TABLE gearset2 AS SELECT gearset.legs, gearset.shoulder, gearset.head, gearset.hands, gearset.chest, gearset.wrist, gearset.neck, gearset.back, gearset.mainhand, gearset.offhand, gearset.ranged, gearset.ring1, gearset.ring2, gearset.bootenchant, gearset.glovesenchant, gearset.weaponenchant, gearset.trinket1, trinket2.id as trinket2, gearset.waist, gearset.feet, gearset.decount, gearset.hit+trinket2.hit+trinket2.bonus_hit as hit, gearset.t7, gearset.t8, gearset.weight+trinket2.weight as weight from gearset LEFT JOIN combined_items trinket2 ON trinket2.bodypart='Trinket'", false);
            db.Query("DROP TABLE gearset", false);
            db.Query("CREATE TABLE gearset AS SELECT * FROM gearset2", false);
            db.Query("DELETE FROM gearset WHERE trinket1=trinket2", false);
            PruneGearsetTable();
            
            //DONE!
            db.Close();
            }         
        
        private void PruneGearsetTable()
        {
            DBConnector db = DBConnector.GetDBConnector("MySQL", "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TrippWS;UID=MySQLAccess;PASSWORD=_MySQL2005_");
            db.Open();
            //remove any set with more than three Dragon's eyes.
            db.Query("DELETE FROM gearset WHERE decount > 3", false);

            //remove any set with too much hit
            db.Query("DELETE FROM gearset WHERE hit > 400", false);
            //prune all but top 1 from each set. Dragon's Eyes are considered separately for now, and only pruned if safe.
            //t7&t8 only pruned if safe also.
            XmlNodeList groups = db.Query("SELECT *, count(*) as count FROM gearset group by hit, decount, t7, t8 order by rand()", false).SelectNodes("//row");
            for (int i = 0; i < groups.Count; i++)
            {
               
                //some queries may completely remove many groups. So if we have less
                //groups then we started with (and we've looked at at least
                //20 groups), reset groups
                if (i %20 ==0 && i<100) //if we get 100 done without removing 10, that's clean enough, so proceed as normal.
                {
                    XmlNodeList newGroups = db.Query("SELECT *, count(*) as count FROM gearset group by hit, decount, t7, t8 order by rand()", false).SelectNodes("//row");
                    if (groups.Count > (newGroups.Count+10)) //i.e, >10 groups gone
                    {
                        groups = newGroups;
                        i=0;
                    }
                }

                XmlDoc group = new XmlDoc();
                group.LoadXml(groups[i].OuterXml);

                //mysql doesn't support subquery, so do it manually.
                //get best weight for this hit
                try
                {
                    string weight = db.Query("SELECT * FROM gearset WHERE decount='" + group.GetNodeValue("//decount") + "' AND t7='" + group.GetNodeValue("//t7") + "' AND t8='" + group.GetNodeValue("//t8") + "' AND hit=" + group.GetNodeValue("//hit") + " ORDER BY weight DESC LIMIT 1", false).SelectSingleNode("//weight").InnerText;
                    db.Query("DELETE FROM gearset WHERE decount >= '" + group.GetNodeValue("//decount") + "' AND t7<=" + group.GetNodeValue("//t7") + " AND t8<=" + group.GetNodeValue("//t8") + " AND hit<=" + group.GetNodeValue("//hit") + " AND weight <(" + weight + "-0.1)",false);

                    //or if more t7 but not enough weight
                    db.Query("DELETE FROM gearset WHERE decount >= '" + group.GetNodeValue("//decount") + "' AND t7>=" + group.GetNodeValue("//t7") + " AND t8<=" + group.GetNodeValue("//t8") + " AND hit<=" + group.GetNodeValue("//hit") + " AND weight <(" + weight + "-" + t7Weight.Text + "-0.1)", false);

                    //or if more t8 but not enough weight
                    db.Query("DELETE FROM gearset WHERE decount >= '" + group.GetNodeValue("//decount") + "' AND t7<=" + group.GetNodeValue("//t7") + " AND t8>=" + group.GetNodeValue("//t8") + " AND hit<=" + group.GetNodeValue("//hit") + " AND weight <(" + weight + "-" + t8Weight.Text + "-0.1)", false);


                    //remove gearsets which have<=hit and less weight, and >= dragon's eyes

                    //db.Query("DELETE FROM gearset WHERE hit=" + group.GetNodeValue("//hit") + " AND id NOT IN SELECT id FROM gearset WHERE hit=" + group.GetNodeValue("//hit") + " ORDER BY weight DESC LIMIT " + limit);
        
                }
                catch (Exception e)
                {
                    //probably means a higher decount gearset got pruned by a lower one having a better rating
                }
            }
            db.Close();
            //full check for inferior builds.
            /*
            XmlNodeList items = db.Query("SELECT * FROM gearset order by weight desc, hit desc").SelectNodes("//row");
            for (int i = 0; i < items.Count; i++)
            {
                if (i%4==0)
                {
                    Thread.Sleep(250);
                }

                XmlDoc item = new XmlDoc();
                item.LoadXml(items[i].OuterXml);

                //delete any set with equal or less hit AND less weight
                db.Query("DELETE FROM gearset WHERE hit<=" + item.GetNodeValue("//hit") + " AND weight<(" + item.GetNodeValue("//weight") + "-0.1)");
            }
            */
        }         
    }

}
